/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.fjs.liyq.dao.impl;

import edu.fjs.liyq.dao.ProductDao;
import edu.fjs.liyq.entity.Product;
import edu.fjs.liyq.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author danny
 */
public class ProductDaoImpl implements ProductDao {

    @Override
    public List<Product> selectPage(int pageNum, int rowCount) {
        List<Product> pl = new ArrayList<>();
        try (Connection con = DBUtil.getConnection()) {
            StringBuilder sql = new StringBuilder("select rownum, b.* ");
            sql.append("from (select rownum rn, a.* from (select t.* from easybuy_product t) a) b ")
                    .append("where rownum <= ? and b.rn > ?");
            PreparedStatement pst = con.prepareStatement(sql.toString());
            pst.setInt(1, rowCount);
            pst.setInt(2, (pageNum - 1) * rowCount);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                Product p = ecapsulate(rs);
                pl.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pl;
    }

    private Product ecapsulate(ResultSet rs) throws SQLException {
        Product p = new Product();
        p.setEpId(rs.getLong("EP_ID"));
        p.setEpName(rs.getString("EP_NAME"));
        p.setEpDescription(rs.getString("EP_DESCRIPTION"));
        p.setEpFileName(rs.getString("EP_FILE_NAME"));
        p.setEpPrice(rs.getDouble("EP_PRICE"));
        p.setEpStock(rs.getInt("EP_STOCK"));
        return p;
    }

    public static void main(String[] args) {
        ProductDao dao = new ProductDaoImpl();
        Product product = new Product();
        product.setEpId(1l);
        product.setEpName("测试商品 事务");
        product.setEpPrice(50.8);
        product.setEpStock(50);
        dao.insert(product);
//        List<Product> pl = dao.selectPage(2, 5);
//        System.out.println("共查询出 " + pl.size() + " 条数据");
//        System.out.println("ID\t商品名\t商品描述\t商品价格\t库存");
//        for (Product product : pl) {
//            System.out.println(product.getEpId() + "\t" + product.getEpName() + "\t" + product.getEpDescription()
//                    + "\t" + product.getEpPrice() + "\t"
//                    + product.getEpStock());
//        }
    }

    @Override
    public int selectCount() {
        int rows = 0;
        try (Connection con = DBUtil.getConnection()) {
            Statement st = con.createStatement();

            ResultSet rs = st.executeQuery("select count(*) from easybuy_product");
            if (rs.next()) {
                rows = rs.getInt(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rows;
    }

    @Override
    public int insert(Product product) {
        int result = 0;
        Connection con = DBUtil.getConnection();
        try {
            // 事务处理
            con.setAutoCommit(false);
            PreparedStatement pst = con.prepareStatement("insert into easybuy_product(EP_ID,EP_NAME,EP_DESCRIPTION,EP_PRICE,EP_STOCK,EP_FILE_NAME)"
                    + " values(easybuy_all.nextval,?,?,?,?,?)");
            pst.setString(1, product.getEpName());
            pst.setString(2, product.getEpDescription());
            pst.setDouble(3, product.getEpPrice());
            pst.setInt(4, product.getEpStock());
            pst.setString(5, product.getEpFileName());
            result  = pst.executeUpdate();
            // 提交
            con.commit();
        } catch (SQLException ex) {
            try {
                con.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(ProductDaoImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(ProductDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ProductDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    @Override
    public Product select(long prodId) {
        Connection con = DBUtil.getConnection();
        try {
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from EASYBUY_PRODUCT where EP_ID="+prodId);
            if (rs.next())
                return ecapsulate(rs);
        } catch (SQLException ex) {
            Logger.getLogger(ProductDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public int update(Product product) {
        int result = 0;
        Connection con = DBUtil.getConnection();
        try {
            PreparedStatement pst = con.prepareStatement("update easybuy_product set EP_NAME=?,EP_DESCRIPTION=?,EP_PRICE=?,EP_STOCK=?,EP_FILE_NAME=?"
                    + " where EP_ID=?");
            pst.setString(1, product.getEpName());
            pst.setString(2, product.getEpDescription());
            pst.setDouble(3, product.getEpPrice());
            pst.setInt(4, product.getEpStock());
            pst.setString(5, product.getEpFileName());
            pst.setLong(6, product.getEpId());
            result  = pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } 
        return result;
    }
    
}
